<?php
/**
 *  學生父母親升級
 * @author cik
 *
 */
class Default_Model_Upgrade_StudDomicile extends Default_Model_Upgrade_Core
{
    protected $_table = 'stud_domicile';
    protected $_targetTable = array('student_parents');

    function upgrade (){
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            $hasData = 0 ;
            // 先判斷是否已有資料
            $query = "SELECT COUNT(*) AS cc FROM student_parents";
            $res = $this->_newdbPdo->query($query)->fetch();
            $hasData = $res['cc'];


            $query = "ALTER TABLE `student_parents`  DROP  FOREIGN KEY  `student_parents_ibfk_1`  ";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_parents`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

            $query = "SELECT a.*,b.teacher_sn  FROM stud_domicile a LEFT JOIN teacher_base b ON a.update_id=b.teach_id" ;
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                if ($row['student_sn']) {
                    // 父
                    $arr = array();
                    $arr['update_time'] = $row['update_time'];
                    $arr['update_sn'] = ($row['teacher_sn'])?$row['teacher_sn']:0;
                    $arr['student_sn'] = $row['student_sn'];
                    if ($hasData) {
                        $find = $this->_newdbPdo->query("SELECT student_sn FROM student_parents
				  WHERE student_sn ='{$arr['student_sn']}'
				  ");
                        if ($find->rowCount() > 0)
                        continue;
                    }

                    if ($row['fath_name']) {
                        $arr['name'] =  $this->iconv($row['fath_name']);
                        $arr['sex'] = 'M';
                        $arr['born_year'] = $row['fath_birthyear'];
                        $arr['alive'] = ($row['fath_alive']=='1')?'Y':'N';
                        $arr['identification_number'] = $row['fath_p_id'];
                        $arr['education_level'] = $row['fath_education'];
                        $arr['graduate_kind'] = $row['fath_grad_kind'];
                        $arr['occupation'] = $this->iconv($row['fath_occupation']);
                        $arr['organ'] = $this->iconv($row['fath_unit']);
                        $arr['organ_title'] = $this->iconv($row['fath_work_name']);
                        $arr['phone_home'] = $this->iconv($row['fath_home_phone']);
                        $arr['phone_office'] = $this->iconv($row['fath_phone']);
                        $arr['phone_mobile'] = $this->iconv($row['fath_hand_phone']);
                        $arr['email'] = $this->iconv($row['fath_email']);
                        $query  = "INSERT INTO  student_parents  (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                        $this->_newdbPdo->exec($query);
                    }
                    // 母
                    if ($row['moth_name']) {
                        $arr = array();
                        $arr['update_time'] = $row['update_time'];
                        $arr['update_sn'] = ($row['teacher_sn'])?$row['teacher_sn']:0;
                        $arr['student_sn'] = $row['student_sn'];

                        $arr['name'] =  $this->iconv($row['moth_name']);
                        $arr['sex'] = 'F';
                        $arr['born_year'] = $row['moth_birthyear'];
                        $arr['alive'] = ($row['moth_alive']=='1')?'Y':'N';
                        $arr['identification_number'] = $row['moth_p_id'];
                        $arr['education_level'] = $row['moth_education'];
                        $arr['graduate_kind'] = $row['moth_grad_kind'];
                        $arr['occupation'] = $this->iconv($row['moth_occupation']);
                        $arr['organ'] = $this->iconv($row['moth_unit']);
                        $arr['organ_title'] = $this->iconv($row['moth_work_name']);
                        $arr['phone_home'] = $this->iconv($row['moth_home_phone']);
                        $arr['phone_office'] = $this->iconv($row['moth_phone']);
                        $arr['phone_mobile'] = $this->iconv($row['moth_hand_phone']);
                        $arr['email'] = $this->iconv($row['moth_email']);
                        $query  = "INSERT INTO  student_parents  (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                        $this->_newdbPdo->exec($query);
                    }
                    //祖父
                    if ($row['grandfath_name']) {
                        $arr = array();
                        $arr['update_time'] = $row['update_time'];
                        $arr['update_sn'] = ($row['teacher_sn'])?$row['teacher_sn']:0;
                        $arr['student_sn'] = $row['student_sn'];
                        $arr['name'] = $this->iconv($row['grandfath_name']);
                        $arr['sex'] = 'M';
                        $arr['alive'] = ($row['grandfath_alive']=='1')?'Y':'N';
                        $query  = "INSERT INTO  student_grandparents  (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                        $this->_newdbPdo->exec($query);
                    }
                    if ($row['grandmoth_name']) {
                        // 祖母
                        $arr = array();
                        $arr['update_time'] = $row['update_time'];
                        $arr['update_sn'] = ($row['teacher_sn'])?$row['teacher_sn']:0;
                        $arr['student_sn'] = $row['student_sn'];
                        $arr['name'] = $this->iconv($row['grandmoth_name']);
                        $arr['sex'] = 'F';
                        $arr['alive'] = ($row['grandmoth_alive']=='1')?'Y':'N';
                        $query  = "INSERT INTO  student_grandparents  (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                        $this->_newdbPdo->exec($query);
                    }
                    //監護人
                    if ($row['guardian_name']) {
                        $arr = array();
                        $arr['update_time'] = $row['update_time'];
                        $arr['update_sn'] = ($row['teacher_sn'])?$row['teacher_sn']:0;
                        $arr['student_sn'] = $row['student_sn'];
                        $arr['name'] = $this->iconv($row['guardian_name']);
                        $arr['address'] =  $this->iconv($row['guardian_address']);
                        $arr['identification_number'] =$this->iconv($row['guardian_p_id']);
                        $relation = intval($row['guardian_relation']);
                        $arr['relation'] = $relation;
                        $arr['organ'] = $this->iconv($row['guardian_unit']);
                        $arr['organ_title'] = $this->iconv($row['guardian_work_name']);
                        $arr['phone_office'] = $this->iconv($row['guardian_phone']);
                        $arr['phone_mobile'] = $this->iconv($row['guardian_hand_phone']);
                        $arr['email'] = $this->iconv($row['guardian_email']);
                        $query  = "INSERT INTO  student_guardian  (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                        $this->_newdbPdo->exec($query);
                    }
                }
            }

            $query = "ALTER TABLE `student_parents`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_parents`
  ADD CONSTRAINT `student_parents_ibfk_1` FOREIGN KEY (`student_sn`) REFERENCES `student` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;
    ";
            $this->_newdbPdo->exec($query);


            $log['memo'] = 'stud_domicile  升級';
            $log['upgrade_time']	= date("Y-m-d h:i:s");

            $this->finishUpgrade($log);

        }
    }
}